-- @owner: zhanghuan
-- @date: 2024-06-19
-- @testpoint: 算术运算符测试

--step1:创建测试表; expect:执行成功
drop table if exists t_ustore_listagg_0001;
create table t_ustore_listagg_0001(col_1 integer,     
col_2 bigint,     col_3 float8,     col_4 decimal(12,6),     
col_5 bool,     col_6 char(30),     col_7 varchar2(50),     
col_8 varchar(30),     col_9 interval day to second,     
col_10 timestamp,     col_11 timestamp,     col_12 smalldatetime,     
col_13 timestamp without time zone,     col_14 blob,     col_15 clob,     
col_16 int[]) with (storage_type=ustore);

--step2:测试表中插入数据; expect:执行成功
drop sequence if exists seq_ustore_listagg_0001;
create sequence seq_ustore_listagg_0001 increment by 1 start with 10;
insert into t_ustore_listagg_0001 values(1,seq_ustore_listagg_0001.nextval,1+445.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0001 values(1,seq_ustore_listagg_0001.nextval-1, 1+445.255,98*0.99, false,lpad('abc','5','@'),lpad('abc','6','b'),rpad('abc','6','e'),(interval '4 5:09:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0001 values(1,seq_ustore_listagg_0001.nextval+2, 1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0001 values(2,seq_ustore_listagg_0001.nextval, 1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0001 values(2,seq_ustore_listagg_0001.nextval-1, 1+445.255,98*0.99, true,lpad('abc','5','@'),lpad('abc','5','b'),rpad('abc','4','e'),(interval
'4 5:09:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');insert into t_ustore_listagg_0001 values(3,seq_ustore_listagg_0001.nextval, 1+445.255,98*0.99, false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');

--step3:算术运算符测试; expect:执行成功
select col_7,col_8,listagg(1,'') within group(order by 1) over(partition by length(col_1)+cos(col_3)+length(to_char(col_4))) a  from t_ustore_listagg_0001 order by 1,2,3;
select col_1+col_16[1],listagg(col_3,'hhh') within group(order by col_3) over(partition by col_1+col_16[1]) b from t_ustore_listagg_0001 order by 1,2;

--step4:清理环境; expect:执行成功
drop sequence if exists seq_ustore_listagg_0001;
drop table if exists t_ustore_listagg_0001;

